
import numpy as np
import pandas as pd
import plotly_express as px

# für Dashboard
from dash import Dash
from dash import dcc
from dash import html
from dash.dependencies import Input, Output
from dash import no_update

# misc
import re

# Wichtig, wenn das Notebook exportiert wird in HTML, dann werden die Grafiken auch im HTML angezeigt
from plotly.offline import init_notebook_mode, iplot
init_notebook_mode(connected=True)

# recommened behaviour for pandas. Avoids warning.
pd.options.mode.copy_on_write = True


# try to load data with standard delimiter
df = pd.read_csv("data_abschlussprojekt.csv", engine='python', nrows=2)
df


df.columns

Index(['# funded_amount# loan_amount# activity# sector# use# country_code# country# region# currency# term_in_months# lender_count# borrower_genders# repayment_interval'], dtype='object')


df = pd.read_csv("data_abschlussprojekt.csv",
                 sep='#',
                 engine="python",
                 skipinitialspace=True,
                 index_col=0)
df


df.columns  # Spalten

Index(['funded_amount', 'loan_amount', 'activity', 'sector', 'use',
       'country_code', 'country', 'region', 'currency', 'term_in_months',
       'lender_count', 'borrower_genders', 'repayment_interval'],
      dtype='object')


print(df.info())  # DatenTypen

<class 'pandas.core.frame.DataFrame'>
Index: 671205 entries, 0 to 671204
Data columns (total 13 columns):
 #   Column              Non-Null Count   Dtype  
---  ------              --------------   -----  
 0   funded_amount       671205 non-null  float64
 1   loan_amount         671205 non-null  float64
 2   activity            671205 non-null  object 
 3   sector              671205 non-null  object 
 4   use                 666972 non-null  object 
 5   country_code        671197 non-null  object 
 6   country             671205 non-null  object 
 7   region              614405 non-null  object 
 8   currency            671205 non-null  object 
 9   term_in_months      671205 non-null  float64
 10  lender_count        671205 non-null  int64  
 11  borrower_genders    666984 non-null  object 
 12  repayment_interval  671205 non-null  object 
dtypes: float64(3), int64(1), object(9)
memory usage: 71.7+ MB
None


# wieviel unique values haben die object spalten
cols = df.columns
for col in cols:
    print(f"{col}: ", df[col].nunique())

funded_amount:  610
loan_amount:  479
activity:  163
sector:  15
use:  423452
country_code:  86
country:  87
region:  12695
currency:  67
term_in_months:  148
lender_count:  503
borrower_genders:  11298
repayment_interval:  4


# potentielle categories
# gibt es verdächtige strings?
cols = ["sector", "country_code", "currency", "repayment_interval"]
# lists zu lang: "activity", "country"

for col in cols:
    strings = []
    for element in df[col].unique():
        strings.append(element)

    # Ausgabe der wirklichen Strings
    print(f"{col}: {strings}")

sector: ['Food', 'Transportation', 'Arts', 'Services', 'Agriculture', 'Manufacturing', 'Wholesale', 'Retail', 'Clothing', 'Construction', 'Health', 'Education', 'Personal Use', 'Housing', 'Entertainment']
country_code: ['PK', 'IN', 'KE', 'NI', 'SV', 'TZ', 'PH', 'PE', 'SN', 'KH', 'LR', 'VN', 'IQ', 'HN', 'PS', 'MN', 'US', 'ML', 'CO', 'TJ', 'GT', 'EC', 'BO', 'YE', 'GH', 'SL', 'HT', 'CL', 'JO', 'UG', 'BI', 'BF', 'TL', 'ID', 'GE', 'UA', 'XK', 'AL', 'CD', 'CR', 'SO', 'ZW', 'CM', 'TR', 'AZ', 'DO', 'BR', 'MX', 'KG', 'AM', 'PY', 'LB', 'WS', 'IL', 'RW', 'ZM', 'NP', 'CG', 'MZ', 'ZA', 'TG', 'BJ', 'BZ', 'SR', 'TH', 'NG', 'MR', 'VU', 'PA', 'VI', 'VC', 'LA', 'MW', 'MM', 'MD', 'SS', 'SB', 'CN', 'EG', 'GU', 'AF', 'MG', nan, 'PR', 'LS', 'CI', 'BT']
currency: ['PKR', 'INR', 'KES', 'NIO', 'USD', 'TZS', 'PHP', 'PEN', 'XOF', 'LRD', 'VND', 'HNL', 'MNT', 'COP', 'GTQ', 'TJS', 'BOB', 'YER', 'KHR', 'GHS', 'SLL', 'HTG', 'CLP', 'JOD', 'UGX', 'BIF', 'IDR', 'GEL', 'UAH', 'EUR', 'ALL', 'CRC', 'XAF', 'TRY', 'AZN', 'DOP', 'BRL', 'MXN', 'KGS', 'AMD', 'PYG', 'LBP', 'WST', 'ILS', 'RWF', 'ZMW', 'NPR', 'MZN', 'ZAR', 'BZD', 'SRD', 'NGN', 'VUV', 'XCD', 'MWK', 'LAK', 'MMK', 'ZWD', 'MDL', 'SSP', 'SBD', 'CNY', 'EGP', 'MGA', 'NAD', 'LSL', 'THB']
repayment_interval: ['irregular', 'bullet', 'monthly', 'weekly']


df['activity'] = df['activity'].astype("category")
df['sector'] = df['sector'].astype("category")
df['use'] = df['use'].astype("string")
df['country_code'] = df['country_code'].astype("category")
df['country'] = df['country'].astype("category")
df['currency'] = df['currency'].astype("category")
df['region'] = df['region'].astype("string")
df['borrower_genders'] = df['borrower_genders'].astype("string")
df['repayment_interval'] = df['repayment_interval'].astype("category")

df.dtypes

funded_amount                float64
loan_amount                  float64
activity                    category
sector                      category
use                   string[python]
country_code                category
country                     category
region                string[python]
currency                    category
term_in_months               float64
lender_count                   int64
borrower_genders      string[python]
repayment_interval          category
dtype: object


df_duplicated = df[df.duplicated(keep=False)]
df_duplicated


df.isna().sum()  # NaNs overview

funded_amount             0
loan_amount               0
activity                  0
sector                    0
use                    4233
country_code              8
country                   0
region                56800
currency                  0
term_in_months            0
lender_count              0
borrower_genders       4221
repayment_interval        0
dtype: int64


df.loc[df["use"].isna(), :]  # NaNs in 'use' auflisten


# Anzahl der NaNs in 'borrower_gender' & 'use' sharing the same row indices? 3888
(df['use'].isna() & df['borrower_genders'].isna()).value_counts()

False    666984
True       4221
Name: count, dtype: int64


# Anzahl der NaNs in 'borrower_gender' & 'use' & 'region' sharing the same row indices? Skewing? spezielles land betroffen? 3888
dfisna = df.loc[(df['use'].isna() & df['borrower_genders'].isna()
                 & df['region'].isna()), :]
dfisna


dfisna['repayment_interval'].unique()

['monthly', 'bullet', 'irregular']
Categories (4, object): ['bullet', 'irregular', 'monthly', 'weekly']


dfisna['region'].unique()

<StringArray>
[<NA>]
Length: 1, dtype: string


df = df.dropna(subset=['use'])  # Löschen von Zeilen, in denen "use" NaN ist
# Löschen von Zeilen, in denen "borrower_genders" NaN ist
df = df.dropna(subset=['borrower_genders'])


# alle NaNs in 'countrycode' sind in 'country' Namibia (countrycode=Na)
dfisna2 = df[df['country_code'].isna()]
dfisna2


df['country_code'] = df['country_code'].cat.add_categories(
    "Na")  # neue Kategorie "Na" zu Category 'Country_code'

df.loc[:, 'country_code'] = df['country_code'].fillna("Na")  # "Na" eintragen
na_country = df[df['country_code'] == "Na"]

na_country


df.loc[:, 'region'] = df['region'].fillna(
    "Not specified")  # Auffuellen mit "Not specified"
df[df['region'] == "Not specified"]  # check


df.describe()


df[df.funded_amount == 100000]


# Commented as it slows down notebook
# fig12 = px.box(df.funded_amount, title='Extremwerte in funded_amount?')
# fig12.update_layout(yaxis_type="log")
# fig12.show()


df[df.loan_amount == 100000]


fig13 = px.box(df.loan_amount, title='Extremwerte in loan_amount?')
fig13.update_layout(yaxis_type="log")
fig13.show()


df[df.lender_count == 2986]


df.sort_values(by='lender_count', ascending=False)


# Commented as it slows down notebook
# fig14 = px.box(df.lender_count,
#                title='Extremwerte in lender_count?'
#               )
# fig14.update_layout(yaxis_type="log")
# fig14.show()


df['funding_rate'] = round((df['funded_amount'] / df['loan_amount']) * 100, 2)
df.head(10)


sortedbyfr_df = df.sort_values(by='funding_rate', ascending=True)
sortedbyfr_df


print(df.info())

<class 'pandas.core.frame.DataFrame'>
Index: 666972 entries, 0 to 671204
Data columns (total 14 columns):
 #   Column              Non-Null Count   Dtype   
---  ------              --------------   -----   
 0   funded_amount       666972 non-null  float64 
 1   loan_amount         666972 non-null  float64 
 2   activity            666972 non-null  category
 3   sector              666972 non-null  category
 4   use                 666972 non-null  string  
 5   country_code        666972 non-null  category
 6   country             666972 non-null  category
 7   region              666972 non-null  string  
 8   currency            666972 non-null  category
 9   term_in_months      666972 non-null  float64 
 10  lender_count        666972 non-null  int64   
 11  borrower_genders    666972 non-null  string  
 12  repayment_interval  666972 non-null  category
 13  funding_rate        666972 non-null  float64 
dtypes: category(6), float64(4), int64(1), string(3)
memory usage: 50.3 MB
None


# number of uniques and unique items in column sector
print(df.loc[:, 'sector'].nunique(), df.loc[:, 'sector'].unique())

15 ['Food', 'Transportation', 'Arts', 'Services', 'Agriculture', ..., 'Health', 'Education', 'Personal Use', 'Housing', 'Entertainment']
Length: 15
Categories (15, object): ['Agriculture', 'Arts', 'Clothing', 'Construction', ..., 'Retail', 'Services', 'Transportation', 'Wholesale']


# number of uniques and unique items in column activity
print(df.loc[:, 'activity'].nunique(), df.loc[:, 'activity'].unique())

163 ['Fruits & Vegetables', 'Rickshaw', 'Transportation', 'Embroidery', 'Milk Sales', ..., 'Event Planning', 'Celebrations', 'Computer', 'Personal Care Products', 'Mobile Transactions']
Length: 163
Categories (163, object): ['Adult Care', 'Agriculture', 'Air Conditioning', 'Animal Sales', ..., 'Weaving', 'Wedding Expenses', 'Well digging', 'Wholesale']


df.loc[:, 'borrower_genders'].unique()

<StringArray>
[                                                                                                                                                                                                                      'female',
                                                                                                                                                                                                               'female, female',
                                                                                                                                                                                                       'female, female, female',
                                                                                                                                                               'female, female, female, female, female, female, female, female',
                                                                                                                                                                                                                         'male',
                                                                                                                                                                                                 'male, male, male, male, male',
                                                                                                                                                                                                 'male, female, female, female',
                                                                                                                                                       'female, female, female, female, female, female, female, female, female',
                                                                                                                                                                                       'female, female, female, female, female',
                                                                                                                                                                                                                   'male, male',
 ...
                                                                                                                           'male, female, male, female, male, female, male, male, female, female, male, male, male, male, male',
                                                                                                                                       'female, female, male, female, female, male, male, male, female, female, female, female',
                                                                                                                     'female, male, female, male, male, male, male, male, female, female, female, male, female, female, female',
                                                                                   'female, female, male, female, male, female, male, male, female, female, male, male, female, male, female, female, female, male, male, male',
                                           'female, female, female, female, female, female, female, male, female, female, female, female, female, female, female, female, female, female, female, female, female, female, male',
                                                                   'female, female, male, male, female, male, male, female, female, male, female, male, female, male, male, male, female, female, female, male, female, female',
                                                                                                                                   'female, female, female, female, male, male, female, female, female, female, female, female',
                                                       'female, female, male, female, female, female, female, female, female, female, male, male, female, female, male, female, female, female, female, female, female, female',
                                                                                                             'male, female, female, female, female, female, female, female, male, male, female, male, female, male, male, male',
 'female, female, female, male, female, female, female, male, female, female, female, male, female, male, female, female, female, female, female, female, female, female, female, female, female, female, female, female, male']
Length: 11297, dtype: string


# Method to count persons (male, females) in list

def person_count(text):
    male_pattern = re.compile(r'\bmale\b', flags=re.IGNORECASE)
    male_count = len(male_pattern.findall(text))
    female_pattern = re.compile(r'\bfemale\b', flags=re.IGNORECASE)
    female_count = len(female_pattern.findall(text))
    return male_count + female_count


# Insert new row "borrower_count"
df['borrower_count'] = df['borrower_genders'].map(person_count)


sumFundsTotal = df.loc[:, 'funded_amount'].sum()  # Summe der Fundings overall
sumFundsTotal

523660950.0


sumLoansTotal = df.loc[:, 'loan_amount'].sum()  # Summe der Loans overall
sumLoansTotal

560438500.0


fundingCapacityNom = sumLoansTotal-sumFundsTotal
fundingCapacityNom

36777550.0


fundingCapacityRatio = sumFundsTotal/sumLoansTotal
fundingCapacityRatio

0.934377188576445


# Anzahl der Projekte (donut fuer die 15 categories)
df_sector = df.groupby(by='sector', as_index=False, observed=False).agg(
    amount_of_projects=("sector", "count")).sort_values(by="amount_of_projects", ascending=False)
df_sector = df_sector.reset_index(drop=True)
df_sector


bottom_seven = df_sector.tail(7)
bottom_seven_sum = bottom_seven['amount_of_projects'].sum()
df_other = pd.DataFrame(
    {'sector': ['Other'], 'amount_of_projects': [bottom_seven_sum]})
df_sector2 = pd.concat([df_sector, df_other], ignore_index=True).sort_values(
    by="amount_of_projects", ascending=False)
df_sector2 = df_sector2.drop(df_sector2.tail(7).index)
df_sector2 = df_sector2.reset_index(drop=True)
df_sector2


# color map for our sectors
sectors = df["sector"].unique()
colorMap = {val: 'lightgrey' for val in sectors}

ourSectors = ["Entertainment", "Wholesale",
              "Health", "Construction", "Manufacturing"]
for val in ourSectors:
    colorMap[val] = "yellow"
# zusätzlich noch Other Kategorie
colorMap["Other"] = "yellow"


df_sector = df.groupby(by='sector', as_index=False, observed=False).agg(amount_of_projects=(
    "sector", "count")).sort_values(by="amount_of_projects", ascending=False)
df_sector = df_sector.reset_index(drop=True)
df_sector

bottom_five = df_sector.tail(5)
bottom_five_sum = bottom_five['amount_of_projects'].sum()
df_other = pd.DataFrame(
    {'sector': ['Other'], 'amount_of_projects': [bottom_five_sum]})
df_sector2 = pd.concat([df_sector, df_other], ignore_index=True).sort_values(
    by="amount_of_projects", ascending=False)
df_sector2 = df_sector2.drop(df_sector2.tail(5).index)
df_sector2 = df_sector2.reset_index(drop=True)
df_sector2


df_sector4 = df_sector2.sort_values(
    by="amount_of_projects", ascending=True).reset_index(drop=True)
df_sector4

gewuenschte_reihenfolge = [
    "Other",
    "Transportation",
    "Arts",
    "Personal Use",
    "Education",
    "Housing",
    "Clothing",
    "Services",
    "Retail",
    "Food",
    "Agriculture",
]


fig1 = px.pie(df_sector4,
              values='amount_of_projects',
              names='sector',
              title='Großteil der Projekte beschäftigt sich mit der Befriedigung existentieller Beduerfnisse',
              hover_name='sector',
              color="sector",
              color_discrete_map=colorMap,
              category_orders={"sector": gewuenschte_reihenfolge},
              # sort=True  # Deaktivieren Sie die automatische Sortierung
              )
fig1.update_traces(textposition='inside',
                   textinfo='label',
                   hole=0.4,
                   direction='counterclockwise',
                   rotation=65,
                   selector=dict(type='pie')
                   )
annotation_text = "Health            1.42%<br>Construction   0.96%<br>Manufacturing 0.96%<br>Entertainment 0.13%<br>Wholesale       0.10%"

fig1.add_annotation(
    text=annotation_text,
    x=0.88,
    y=0.55,
    ax=0.51,
    ay=0.51,
    showarrow=True,
    arrowhead=2,
    font=dict(size=14),
    align="left"
)


fig1.add_annotation(text=' ', x=0.725, y=0.65, ax=38.0, ay=0,
                    showarrow=True, arrowhead=2, arrowwidth=2.0, font={"size": 14})
fig1.show()


df_grouped3 = df.groupby(by=["sector"], as_index=False, observed=False).agg(mean_loan_amount=(
    "loan_amount", "mean")).sort_values(by="mean_loan_amount", ascending=False)
df_grouped3.reset_index()

fig2 = px.bar(df_grouped3,
              x="sector",
              y="mean_loan_amount",
              color='sector',
              color_discrete_map=colorMap,
              labels={"mean_loan_amount": "Median der Zielbeträge in USD",
                      "sector": "Oberkategorie"},
              title='Vier unserer fokussierten Bereiche haben größere mittlere Zielbeträge als der Median über alle Projekte',
              template="plotly_white",
              )

median = df_grouped3["mean_loan_amount"].median()
fig2.add_hline(y=median, line_width=2, line_dash="dash")

fig2.add_annotation(text='Mittlerer Zielbetrag über Median über alle Projekte',
                    x=1.3, y=1500, ax=10, ay=-40, showarrow=True, arrowhead=2, arrowwidth=2.0)

fig2.show()


# Only select Country Peru
df_selected = df.loc[df["country"] == "Peru", :]

# categorize this column into buckets 1-4, 5-9, ... Persons
bucketsize = 5
mapRangeToString = {0: "1-4", 5: "5-9", 10: "10-14",
                    15: "15-19", 20: "20-24", 25: "25-30"}
df_selected.loc[:, 'borrower_countCategory'] = (
    df_selected.loc[:, 'borrower_count'].copy() // bucketsize)*bucketsize
df_selected.loc[:, 'borrower_countCategory'] = df_selected.loc[:,
                                                               'borrower_countCategory'].copy().map(mapRangeToString)

df_grouped = df_selected.groupby(by=["sector", "borrower_countCategory"], observed=False).agg(
    meadian_loan=("loan_amount", "median"))

categoriesSortedByTotalSum = ["Agriculture", "Food", "Arts", "Retail", "Education", "Construction", "Construction",
                              "Manufacturing", "Clothing", "Entertainment", "Transportation", "Personal Use", "Services", "Health", "Housing", "Wholesale"]

df_matrix = df_grouped.pivot_table(
    index='borrower_countCategory', columns='sector', values='meadian_loan', aggfunc='median')
df_matrix
# Sort columns by given oder of categoriesSortedByTotalSum list
df_matrix_sorted = df_matrix.loc[:, categoriesSortedByTotalSum]

# Sort rows by given order of mapRangeToString to have categories 1-4, 5-9, ... in matrix sorted descending
borrowerOder = list(mapRangeToString.values())
borrowerOder.reverse()
df_matrix_sorted = df_matrix_sorted.loc[borrowerOder, :]

fig3 = px.imshow(df_matrix_sorted,
                 color_continuous_scale="YlOrRd",
                 labels={"y": "Anzahl der am Projekt Beteiligten",
                         "x": "Oberkategorie der Projekte",
                         "color": "Median der Zielbeträge in USD"},
                 title="Die möglichen Wachstums-Oberkategorien haben keine Projekte mit mehr als 20 Personen",
                 text_auto=True,
                 template="simple_white",
                 height=800)

fig3.add_hline(y=1.49, line_width=2, line_dash="dash",
               line_color="black", opacity=1.0)
fig3.add_vline(x=3.5, line_width=2, line_dash="dash",
               line_color="black", opacity=1.0)

# highlight insight that focused sections have less then 20  project participants
fig3.add_annotation(text='Unsere fokussierten Kategorien haben alle weniger als 20 Beteiligte',
                    x=8.0, y=1.0, showarrow=False, font={"size": 20})
for x in [5, 6, 8, 12, 14]:
    fig3.add_annotation(text='', x=x, y=1.45, showarrow=True,
                        arrowhead=2, arrowcolor="sandybrown", arrowwidth=2.0)

# highlight insight that Arts and Retail have lower median in project loans in category 20-25 people than in category 15-19 people
fig3.add_annotation(text='Zielbetrag ist weniger als bei 15-19 Beteiligten',
                    x=2.0, y=0.45, ax=0.0, showarrow=True, arrowhead=2, arrowwidth=2.0)
fig3.add_annotation(text=' ', x=3.0, y=0.45, ax=0.0, showarrow=True,
                    arrowhead=2, arrowwidth=2.0, font={"size": 15})

fig3.show()


# 1. App erstellen
my_app = Dash(__name__)

# 2. Layout erstellen
# Überschrift
my_app.layout = html.Div(children=[
    html.H1(children="Sektoren je Land", style={"textAlign": "center"}),
    # Daten auswählen
    dcc.Dropdown(
        id="graph_drop",
        options=[{'label': country, 'value': country}
                 for country in df['country'].unique()],
        value="Afghanistan",
        style={"width": "260px"}
    ),
    # Grafik
    dcc.Graph(id="theGraph", figure={}, style={
              'width': '800px', 'height': '600px'})
])

# 3. Callback erstellen


@my_app.callback(
    # 1. IMMER die Outputs
    Output(component_id="theGraph", component_property="figure"),

    # 2. IMMER die Inputs
    Input(component_id="graph_drop", component_property="value"),
)
def update_graph(selected_country):
    df_select = df[df["country"] == selected_country]
    gruppierte_daten = df_select.groupby(
        by="sector",  observed=False).size().reset_index(name="count")

    donut_chart = px.pie(gruppierte_daten, values="count", names="sector", title=f"Sektoren in {selected_country}",
                         labels={"sector": "Sektor", "count": "Anzahl"},
                         hole=0.4,
                         color="sector",
                         color_discrete_map={
                             "Agriculture": "#B4CF66",
                             "Food": "#44803F",
                             "Retail": "#AE65CF",
                             "Other": "#8A818F",
                             "Services": "#E0D424",
                             "Personal Use": "#F2C305",
                             "Housing": "#A67458",
                             "Clothing": "#88D6D6",
                             "Education": "#5C90B5",
                             "Entertainment": "#F24405",
                             "Wholesale": "#B87FE0",
                             "Arts": "#00B3AD",
                             "Transportation": "#575153",
                             "Health": "#3FA9F5",
                             "Construction": "#E06F24",
                             "Manufacturing": "#8856D8"
                         }
                         )

    return donut_chart


# 4. App starten
my_app.run_server(jupyter_mode="external", port=8106)

Dash app running on http://127.0.0.1:8106/


fig4 = px.bar(df_sector,
              y='amount_of_projects',
              x='sector',
              title='Distribution of projects',
              color='sector',
              color_discrete_sequence=px.colors.qualitative.Set2
              )
fig4.show()


fig1 = px.pie(df_sector,
              values='amount_of_projects',
              names='sector',
              title='Distribution of projects',
              color_discrete_sequence=px.colors.qualitative.Set2
              )
fig1.update_traces(textposition='inside', textinfo='label+percent')
fig1.show()


df.sort_values(by='funding_rate', ascending=False).head(10)


# funding rate bins - welche sectors?

df['funding_rate_bin'] = pd.cut(df['funding_rate'], bins=3, labels=[
                                'Low', 'Medium', 'High'])
grouped = df.groupby(['funding_rate_bin', 'sector']
                     ).size().reset_index(name='count')
top_categories = grouped.groupby('funding_rate_bin').apply(
    lambda x: x.nlargest(5, 'count')).reset_index(drop=True)
print(top_categories)

   funding_rate_bin        sector   count
0               Low   Agriculture    5516
1               Low        Retail    3747
2               Low          Food    3152
3               Low      Services    1806
4               Low       Housing    1284
5            Medium   Agriculture    6065
6            Medium        Retail    4760
7            Medium          Food    3527
8            Medium       Housing    2060
9            Medium      Services    1521
10             High   Agriculture  167640
11             High          Food  129067
12             High        Retail  115251
13             High      Services   41369
14             High  Personal Use   35006


df_grouped2 = df.groupby(by=["sector"], as_index=False).agg(sum_loan_amount=(
    "loan_amount", "sum")).sort_values(by="sum_loan_amount", ascending=False)
fig3 = px.bar(df_grouped2,
              x="sector",
              y="sum_loan_amount",
              color='sector',
              color_discrete_sequence=px.colors.qualitative.Set2
              )
fig3.show()
df_grouped2.reset_index()

	# funded_amount# loan_amount# activity# sector# use# country_code# country# region# currency# term_in_months# lender_count# borrower_genders# repayment_interval
0#300.0#300.0#Fruits & Vegetables#Food#To buy seasonal	fresh fruits to sell. #PK#Pakistan#Lahore#PKR...
1#575.0#575.0#Rickshaw#Transportation#to repair and maintain the auto rickshaw used in their business.#PK#Pakistan#Lahore#PKR#11.0#14#female	female#irregular

	funded_amount	loan_amount	term_in_months	lender_count
count	666972.000000	666972.000000	666972.00000	666972.000000
mean	785.131835	840.272905	13.73022	20.551025
std	1128.005848	1187.875622	8.59619	28.366363
min	0.000000	25.000000	1.00000	0.000000
25%	250.000000	275.000000	8.00000	7.000000
50%	450.000000	500.000000	13.00000	13.000000
75%	900.000000	1000.000000	14.00000	24.000000
max	100000.000000	100000.000000	158.00000	2986.000000

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval	funding_rate	borrower_count
338159	3400.0	3000.0	Farm Supplies	Agriculture	to pay for wires for the grape orchard, cover ...	AM	Armenia	Hoktember village, Armavir region	USD	38.0	84	male	monthly	113.33	1
277188	425.0	400.0	General Store	Retail	to buy beverages, rice, laundry detergent, sug...	MZ	Mozambique	Boane, Maputo	MZN	17.0	11	male	monthly	106.25	1
0	300.0	300.0	Fruits & Vegetables	Food	To buy seasonal, fresh fruits to sell.	PK	Pakistan	Lahore	PKR	12.0	12	female	irregular	100.00	1
437814	150.0	150.0	Primary/secondary school costs	Education	to pay school fees for her children.	RW	Rwanda	Not specified	RWF	5.0	6	female	irregular	100.00	1
437817	525.0	525.0	Agriculture	Agriculture	to buy fingerlings and care for the fish.	CO	Colombia	San Jose de Uré	COP	17.0	15	male	bullet	100.00	1
437818	1400.0	1400.0	Farming	Agriculture	to buy fertilizer, pesticide and seeds to grow...	KH	Cambodia	Kratie Province, Kratie District	KHR	14.0	51	female, female, female, female	monthly	100.00	4
437819	175.0	175.0	Farming	Agriculture	to purchase improved farm inputs that will inc...	NG	Nigeria	Kaduna	NGN	19.0	6	male	bullet	100.00	1
437822	350.0	350.0	Farming	Agriculture	to purchase improved farm inputs that will inc...	NG	Nigeria	Kaduna	NGN	19.0	11	male	bullet	100.00	1
437823	275.0	275.0	Restaurant	Food	to buy merchandise.	CM	Cameroon	Yaoundé	XAF	15.0	11	female	monthly	100.00	1
437824	275.0	275.0	Pigs	Agriculture	to buy more pigs to raise and to purchase food...	CO	Colombia	Sahagun	COP	14.0	8	male	bullet	100.00	1

Abschlussprojekt: Kiva funding platform¶

CRISP DM: Business Understanding¶

CRISP DM: Data Understanding¶

Datenaufbereitung¶

Bibs importieren¶

Daten einlesen¶

Objekte kennenlernen¶

DatenTypen Konvertieren¶

Duplicate¶

systemseitig Fehlende Werte - NaN¶

fehlende Werte in 'use' und 'borrower_genders' verwerfen,¶

Nan in country Code für land Nambiba mit 'Na' befüllen¶

Umgang mit Fehlende Werten in der Spalte 'region', ersetzen mit 'Not specified'¶

Extremwerte¶

Ergebnis Ausreißer/Extremwerte¶

Zusätzliche KPIs¶

Funding Rate¶

Anzahl der am Projekt beteiligten Personen¶

EDA¶

Wer sind wir?¶

In welchen Oberkategorien sind die meisten Projekte?¶

Analyse¶

deskriptiv¶

analytisch¶

Welche Oberkategorien haben höchste Zielbetragsgröße¶

Analyse¶

deskriptiv¶

analytisch¶

Wie hoch sind die Zielbetragsgrößen in den Kategorien nach Anzahl der am Projekt beteiligten Personen?¶

Analyse¶

Deskriptiv¶

Analytisch¶

Dashboard¶

Zusätzliche Analysen¶

KPI¶

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
140	2975.0	2975.0	Food Production/Sales	Food	<NA>	TZ	Tanzania	<NA>	TZS	10.0	110	<NA>	monthly
145	1200.0	1200.0	Personal Expenses	Personal Use	<NA>	PE	Peru	<NA>	PEN	20.0	44	<NA>	monthly
170	4250.0	4250.0	Catering	Food	<NA>	TZ	Tanzania	<NA>	TZS	10.0	116	<NA>	monthly
412	2350.0	2350.0	Beauty Salon	Services	<NA>	TZ	Tanzania	<NA>	TZS	10.0	75	<NA>	monthly
414	725.0	725.0	Agriculture	Agriculture	<NA>	SV	El Salvador	<NA>	USD	20.0	19	<NA>	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671151	0.0	25.0	Livestock	Agriculture	<NA>	KE	Kenya	<NA>	KES	13.0	0	<NA>	monthly
671174	0.0	25.0	Games	Entertainment	<NA>	KE	Kenya	<NA>	KES	13.0	0	<NA>	monthly
671178	0.0	25.0	Livestock	Agriculture	<NA>	KE	Kenya	<NA>	KES	13.0	0	<NA>	monthly
671185	0.0	25.0	Livestock	Agriculture	<NA>	KE	Kenya	<NA>	KES	13.0	0	<NA>	monthly
671202	0.0	25.0	Games	Entertainment	<NA>	KE	Kenya	<NA>	KES	13.0	0	<NA>	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
202537	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	6.0	162	female	bullet
202823	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	6.0	159	male	bullet
344929	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	120	female	bullet
351177	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	7.0	126	male	bullet
420953	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	118	female	bullet
421218	4000.0	4000.0	Wholesale	Wholesale	purchase solar lighting products for sale to l...	NaN	Namibia	Rundu	NAD	7.0	150	male	bullet
487207	5100.0	5100.0	Renewable Energy Products	Retail	to pay for stock of solar lights and cell phon...	NaN	Namibia	Katima Mulilo	NAD	7.0	183	male	bullet
487653	5000.0	5000.0	Wholesale	Wholesale	to maintain a stock of solar lights and cell p...	NaN	Namibia	Oshakati	NAD	7.0	183	female	bullet

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
5	250.0	250.0	Services	Services	purchase leather for my business using ksh 20000.	KE	Kenya	Not specified	KES	4.0	6	female	irregular
49	450.0	450.0	General Store	Retail	to stock his store.	SV	El Salvador	Not specified	USD	14.0	18	male	monthly
54	225.0	225.0	Food Market	Food	to purchase various seasonal items to resell: ...	SN	Senegal	Not specified	XOF	14.0	7	female	monthly
67	125.0	125.0	Energy	Services	purchase solar lanterns for resale.	KE	Kenya	Not specified	KES	3.0	6	male	irregular
70	2000.0	2000.0	Retail	Retail	to install a display window and a sunshade for...	IQ	Iraq	Not specified	USD	15.0	71	male	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671194	0.0	25.0	Livestock	Agriculture	Kiva Coordinator fixed issue loan (no longer v...	KE	Kenya	Not specified	KES	13.0	0	female, female	monthly
671197	0.0	25.0	Livestock	Agriculture	Pretend the issue with loan got addressed by K...	KE	Kenya	Not specified	KES	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	Not specified	KES	13.0	1	female	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	Not specified	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	Not specified	KES	13.0	0	female	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
70499	100000.0	100000.0	Agriculture	Agriculture	create more than 300 jobs for women and farmer...	HT	Haiti	Les Cayes	USD	75.0	2986	female	irregular
509048	50000.0	50000.0	Poultry	Agriculture	to purchase chicken feed & a delivery vehicle ...	TZ	Tanzania	Dar es Salaam	USD	14.0	1765	female	irregular
631904	50000.0	50000.0	Agriculture	Agriculture	double cashew nut export output and hire about...	CI	Cote D'Ivoire	Kolia	USD	10.0	1706	female	irregular
565733	50000.0	50000.0	Agriculture	Agriculture	to pay 600 farming families 100% above market ...	EC	Ecuador	Quito	USD	14.0	1689	female	irregular
614922	50000.0	50000.0	Water Distribution	Services	to set up 13 new clean water businesses in nor...	GH	Ghana	Tamale	USD	14.0	1688	female	irregular
...	...	...	...	...	...	...	...	...	...	...	...	...	...
489599	0.0	10000.0	Services	Services	allows us to market and sell...	US	United States	Not specified	USD	36.0	0	male	bullet
665704	0.0	250.0	Cereals	Food	to buy cereals to sell in her local market.	KE	Kenya	Nyamira	KES	14.0	0	female	monthly
665697	0.0	750.0	Tailoring	Services	to invest in the purchase of merchandise: pape...	CO	Colombia	Bogotà	COP	21.0	0	female	monthly
540934	0.0	1500.0	Services	Services	me boost our advertising and other marketing e...	US	United States	Not specified	USD	6.0	0	female	bullet
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	Not specified	KES	13.0	0	female	monthly

	sector	amount_of_projects
0	Agriculture	179221
1	Food	135746
2	Retail	123758
3	Services	44696
4	Personal Use	36246
5	Housing	33571
6	Clothing	32478
7	Education	30837
8	Transportation	15455
9	Arts	11994
10	Health	9172
11	Construction	6194
12	Manufacturing	6158
13	Entertainment	820
14	Wholesale	626

	index	sector	sum_loan_amount
0	0	Agriculture	142107050.0
1	6	Food	120509725.0
2	11	Retail	97322275.0
3	12	Services	47297375.0
4	2	Clothing	36976400.0
5	4	Education	30773050.0
6	8	Housing	23513225.0
7	10	Personal Use	14880400.0
8	1	Arts	12107800.0
9	13	Transportation	10944850.0
10	7	Health	9788100.0
11	3	Construction	6531950.0
12	9	Manufacturing	5385475.0
13	5	Entertainment	1366650.0
14	14	Wholesale	934175.0